﻿CREATE TABLE [dbo].[BugNet_Issues] (
    [IssueId]                  INT              IDENTITY (1, 1) NOT NULL,
    [IssueTitle]               NVARCHAR (500)   NOT NULL,
    [IssueDescription]         NVARCHAR (MAX)   NOT NULL,
    [IssueStatusId]            INT              NULL,
    [IssuePriorityId]          INT              NULL,
    [IssueTypeId]              INT              NULL,
    [IssueCategoryId]          INT              NULL,
    [ProjectId]                INT              NOT NULL,
    [IssueAffectedMilestoneId] INT              NULL,
    [IssueResolutionId]        INT              NULL,
    [IssueCreatorUserId]       UNIQUEIDENTIFIER NOT NULL,
    [IssueAssignedUserId]      UNIQUEIDENTIFIER NULL,
    [IssueOwnerUserId]         UNIQUEIDENTIFIER NULL,
    [IssueDueDate]             DATETIME         CONSTRAINT [DF_BugNet_Issues_DueDate] DEFAULT ('1/1/1900 12:00:00 AM') NULL,
    [IssueMilestoneId]         INT              NULL,
    [IssueVisibility]          INT              NOT NULL,
    [IssueEstimation]          DECIMAL (5, 2)   CONSTRAINT [DF_BugNet_Issues_Estimation] DEFAULT ((0)) NOT NULL,
    [IssueProgress]            INT              CONSTRAINT [DF_BugNet_Issues_IssueProgress] DEFAULT ((0)) NOT NULL,
    [DateCreated]              DATETIME         CONSTRAINT [DF_BugNet_Issues_DateCreated] DEFAULT (getdate()) NOT NULL,
    [LastUpdate]               DATETIME         NOT NULL,
    [LastUpdateUserId]         UNIQUEIDENTIFIER NOT NULL,
    [Disabled]                 BIT              CONSTRAINT [DF_BugNet_Issues_Disabled] DEFAULT ((0)) NOT NULL,
    CONSTRAINT [PK_BugNet_Issues] PRIMARY KEY CLUSTERED ([IssueId] ASC),
    CONSTRAINT [FK_BugNet_Issues_Users] FOREIGN KEY ([IssueAssignedUserId]) REFERENCES [dbo].[Users] ([UserId]),
    CONSTRAINT [FK_BugNet_Issues_Users1] FOREIGN KEY ([IssueOwnerUserId]) REFERENCES [dbo].[Users] ([UserId]),
    CONSTRAINT [FK_BugNet_Issues_Users2] FOREIGN KEY ([LastUpdateUserId]) REFERENCES [dbo].[Users] ([UserId]),
    CONSTRAINT [FK_BugNet_Issues_Users3] FOREIGN KEY ([IssueCreatorUserId]) REFERENCES [dbo].[Users] ([UserId]),
    CONSTRAINT [FK_BugNet_Issues_BugNet_ProjectCategories] FOREIGN KEY ([IssueCategoryId]) REFERENCES [dbo].[BugNet_ProjectCategories] ([CategoryId]),
    CONSTRAINT [FK_BugNet_Issues_BugNet_ProjectIssueTypes] FOREIGN KEY ([IssueTypeId]) REFERENCES [dbo].[BugNet_ProjectIssueTypes] ([IssueTypeId]),
    CONSTRAINT [FK_BugNet_Issues_BugNet_ProjectMilestones] FOREIGN KEY ([IssueMilestoneId]) REFERENCES [dbo].[BugNet_ProjectMilestones] ([MilestoneId]),
    CONSTRAINT [FK_BugNet_Issues_BugNet_ProjectMilestones1] FOREIGN KEY ([IssueAffectedMilestoneId]) REFERENCES [dbo].[BugNet_ProjectMilestones] ([MilestoneId]),
    CONSTRAINT [FK_BugNet_Issues_BugNet_ProjectPriorities] FOREIGN KEY ([IssuePriorityId]) REFERENCES [dbo].[BugNet_ProjectPriorities] ([PriorityId]),
    CONSTRAINT [FK_BugNet_Issues_BugNet_ProjectResolutions] FOREIGN KEY ([IssueResolutionId]) REFERENCES [dbo].[BugNet_ProjectResolutions] ([ResolutionId]),
    CONSTRAINT [FK_BugNet_Issues_BugNet_Projects] FOREIGN KEY ([ProjectId]) REFERENCES [dbo].[BugNet_Projects] ([ProjectId]) ON DELETE CASCADE,
    CONSTRAINT [FK_BugNet_Issues_BugNet_ProjectStatus] FOREIGN KEY ([IssueStatusId]) REFERENCES [dbo].[BugNet_ProjectStatus] ([StatusId])
);

